﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Script.Serialization;
using System.Data;
using System.Data.Sql;
using System.Data.SqlClient;
using System.Configuration;

namespace APIB
{
    public class apibmember
    {

        public string getConn()
        {
            return ConfigurationManager.ConnectionStrings["IFSModel.Properties.Settings.IFN_dbConnectionString"].ConnectionString.ToString();  
        }

        public string GetallMember()
        {
            var dataContext = new dbDataContext ();  

            var db = from p in dataContext.Members select p; 


            var serializer = new JavaScriptSerializer();
            return serializer.Serialize(db);
        }


        public DataTable getdataviewby(int viewby = 0)
        {
            SqlConnection Conn = new SqlConnection(getConn());
            SqlDataAdapter adapter = new SqlDataAdapter();

            string query = "";


            if (viewby == 0)
            {
                query = @"select top 200 a.firstname + ' ' + a.lastname as Fullname,
                            b.createdate,b.LeagueID,b.StarBet,c.liveA,c.liveB, 
                            CASE 
                                  WHEN b.Selectteam = 'liveA' THEN c.liveA 
                                  WHEN b.Selectteam = 'liveB' THEN c.liveB
                            END as teamselected
                            ,c.score,c.matchTime,d.Mpt_Cal_Point,d.Mpt_Cal_Star
                            from members a 
                            inner join Members_play b on a.memid = b.memid
                            inner join match c on b.matchid = c.id  
                            inner join Members_point_tran d on b.MempID = d.RefID
                            where 1 = 1  ";
            }
            if (viewby == 0)
            {
                query += " and IsDone = 0 order by b.createdate desc"; 
            }

            if (viewby == 1)
            {
                query += " and IsDone = 1 order by b.createdate desc"; 
            }

            adapter.SelectCommand = new SqlCommand(query, Conn);

            DataTable dt = new DataTable();

            //if (!string.IsNullOrEmpty(memid))
            //{
            //    adapter.SelectCommand.Parameters.AddWithValue("@memid", memid);
            //    adapter.SelectCommand.Parameters.AddWithValue("@active", 0);
            //}


            Conn.Open();
            try
            {
                adapter.Fill(dt);
                //dt.DefaultView.Sort = "sorting ASC";
            }
            catch
            {
                Conn.Close();
                dt = null;
            }
            finally
            {
                Conn.Close();
            } 

            return dt;

        }
         
        



    }
}